<?php
/*
 * ebiz front
 *
 * ProductItemDao Model
 *
 * @author allen
 *
 */
require_once '../app/core/models/BaseDao.php';
require_once '../app/core/db/DBUtil.class.php';

class ProductItemDaoTest extends BaseDao
{

    private $tableName = "product_items";

    public function __construct($dbUtil){
        parent::__construct($dbUtil);
    }

    public function getByProductId($pid){
        $param = array($pid);
        $sql = "select * from $this->tableName where product_id = ? and is_delete = 0";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getById($id){
        $param = array($id);
        $sql = "select * from $this->tableName where id = ? and is_delete = 0";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getByIds($ids){
    	$ids = mysql_escape_string($ids);
        $param = array();
        $sql = "select * from $this->tableName where id in ( $ids ) and is_delete = 0";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getByPids($pids){     //give the string pid of separator by ','  to find all product info base pid
    	$pids = mysql_escape_string($pids);
        $param = array();
        $sql = "select * from $this->tableName where product_id in ($pids) and is_delete = 0";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getOtherByPid($pid, $itemId){     //give the exclude item id
        $param = array($pid, $itemId);
        $sql = "select * from $this->tableName where product_id = ? and is_delete = 0 and id <> ?";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getRelateProductByProductId($pid){
        $param = array($pid);
        $sql = "select * from $this->tableName  where product_id = ? and is_delete = 0 and id <> ?";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getDistinctAttributeByProductId($pid){  //get the attribute
        $param = array($pid);
//        $sql = "select distinct `attribute_1`,`attribute_2`,`attribute_3`,`attribute_4`,`attribute_5` from".$this->tableName."where product_id=?";
        $sql = "select distinct attribute_1,attribute_2,attribute_3,attribute_4,attribute_5 from ".$this->tableName." where product_id=?";
        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getShoppingcartInfoByCustomerId($customerId){
    	$param = array($customerId);
//    	$sql = "select sc.id as scId , pi.id as pItemId ,  pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 , pi.sale_price ,  p.title ,  pim.name , pim.folder , sc.quantity  from  $this->tableName pi join products p on p.id = pi.product_id  join shopping_carts sc on sc.product_item_id = pi.id  and sc.is_delete = 0  and customer_id = ?  left join product_images pim on pim.item_id = pi.id and pim.default_item_image = 1 and pim.is_delete = 0 where pi.id in ( $itemIds ) and  pi.is_delete = 0 and p.is_delete = 0 ";
        //do not judge is_delete field 0611 modify
//    	$sql = "select sc.id as scId , pi.id as pItemId ,  pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 , pi.sale_price ,  p.title ,  pim.name , pim.folder , sc.quantity  from  $this->tableName pi join products p on p.id = pi.product_id  join shopping_carts sc on sc.product_item_id = pi.id  and sc.is_delete = 0  and customer_id = ?  left join product_images pim on pim.item_id = pi.id and pim.default_item_image = 1 and pim.is_delete = 0 where pi.is_delete = 0 and p.is_delete = 0 ";
    	$sql = "select sc.id as scId , sc.quantity  ,  pi.upc  ,
    	pi.id as pItemId , pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 , pi.sale_price ,
    	p.title ,  pim.name , pim.folder ,p.url_title, p.id as pId 
    	from  $this->tableName pi
    	join products p on p.id = pi.product_id
    	join shopping_carts sc on sc.product_item_id = pi.id  and sc.is_delete = 0  and customer_id = ?
    	left join product_images pim on pim.item_id = pi.id and pim.default_item_image = 1 and pim.is_delete = 0
    	where pi.is_delete = 0 and p.is_delete = 0 
    	group by pi.id
    	";

        return $this->dbUtil->SelectData($sql, $param);
    }

    public function getShoppingCartInfoByItemId($itemId){
    	$param = array($itemId);
    	$sql = "select  pi.id as pItemId ,  pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 , pi.sale_price , pi.upc ,
    	p.title ,  p.url_title , p.id as pId, 
    	pim.name , pim.folder
    	from  product_items pi join products p on p.id = pi.product_id
    	left join product_images pim on pim.item_id = pi.id and pim.default_item_image = 1 and pim.is_delete = 0
    	where    pi.id = ?   and    pi.is_delete = 0 and p.is_delete = 0 
    	group by pi.id
    	";

    	return $this->dbUtil->SelectData($sql, $param);
    }

    public function getDefaultItemByProductId($pid){
        $param = array($pid);
        $sql = "select * from $this->tableName where product_id = ? and default_item = 1 and is_delete = 0";
        return $this->dbUtil->SelectData($sql, $param);
    }

//    public function getShoppingcartInfoToCheckoutByCustomerId($customerId){ //move to shoppingcartDao
//        $param = array($customerId);
//        //not use product image table //modify at 0605
//        $sql = "select sc.id as scId , pi.id as pItemId ,
//        pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 ,
//        pi.sale_price ,  p.title , sc.quantity
//        from  $this->tableName pi join products p on p.id = pi.product_id
//        join shopping_carts sc on sc.product_item_id = pi.id  and sc.is_delete = 0  and customer_id = ? and sc.status = 1
//        where pi.is_delete = 0 and p.is_delete = 0 ";
////        $sql = "select sc.id as scId , pi.id as pItemId ,  pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 , pi.sale_price ,  p.title ,  pim.name , pim.folder , sc.quantity  from  $this->tableName pi join products p on p.id = pi.product_id  join shopping_carts sc on sc.product_item_id = pi.id  and sc.is_delete = 0  and customer_id = ? and sc.status = 1 left join product_images pim on pim.item_id = pi.id and pim.default_item_image = 1 and pim.is_delete = 0 where pi.is_delete = 0 and p.is_delete = 0 ";
////        $sql = "select sc.id as scId , pi.id as pItemId ,  pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 , pi.sale_price ,  p.title ,  pim.name , pim.folder , sc.quantity  from  $this->tableName pi join products p on p.id = pi.product_id  join shopping_carts sc on sc.product_item_id = pi.id  and sc.is_delete = 0  and customer_id = ?                   left join product_images pim on pim.item_id = pi.id and pim.default_item_image = 1 and pim.is_delete = 0 where pi.is_delete = 0 and p.is_delete = 0  and sc.status = 1 ";
//        return $this->dbUtil->SelectData($sql, $param);
//    }

    /**
     * when checkout success then delete the quantity
     */
    public function updateInventoryByItemId($itemId, $qyt){
        $param = array($qyt, $itemId);
    	$sql = "update $this->tableName set inventory = inventory - ? where id = ? and is_delete=0";
    	return $this->dbUtil->ExecuteQuery($sql,$param);
    }

    /**
     * use in shoppingCartService, judge the user bought quantity
     */
//    public function getItemInfoByItemIds($itemIds, $customerId){// do not to judge is_delete field
    public function getItemInfoByItemIds($itemIds){// do not to judge is_delete field
        $itemIds = mysql_escape_string($itemIds);
    	$param = array();
//        $param = array($customerId);
//        $sql = "select pi.id as pItemId , pi.inventory ,  pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 , pi.sale_price , pi.is_delete as piIsDelete ,
//				p.title , p.is_delete as pIsDelete,
//				sc.id as scId
//				from product_items pi
//				join products p on p.id = pi.product_id
//				join shopping_carts sc on sc.product_item_id = pi.id and sc.is_delete = 0 and sc.customer_id = ?
//				where pi.id  in  ( $itemIds ) ";
        $sql = "select pi.id as pItemId , pi.inventory ,  pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 , pi.sale_price , pi.is_delete as piIsDelete ,
				p.title , p.is_delete as pIsDelete
				from $this->tableName pi
				join products p on p.id = pi.product_id
				where pi.id  =  ( $itemIds ) ";//modify 0701 $itemIds is one item id//because use in select query itemid and qty not match case
        return $this->dbUtil->SelectData($sql, $param);
    }

//    /**
//     * get the sale price info from product,product_item to get the info to show prompt info
//     */
//    public function getsalePriceInfoByItemId($itemIds){
//        $itemIds = mysql_escape_string($itemIds);
//        $param = array();
//        $sql = "select pi.id as pItemId , pi.inventory ,  
//        pi.attribute_1 , pi.value_1 , pi.attribute_2 , pi.value_2 ,  pi.attribute_3 , pi.value_3 ,  pi.attribute_4 , pi.value_4,  pi.attribute_5, pi.value_5 , pi.sale_price ,
//        pi.is_delete as piIsDelete , 
//        p.title , p.is_delete as pIsDelete,
//        from $this->tableName pi
//        join products p on p.id = pi.product_id  
//        where pi.id  in  ( $itemIds ) ";
//        return $this->dbUtil->SelectData($sql, $param);
//    }
    
    //give the category id to get all item id
    public function getItemInfoByCategoryIds($cids){
    	$cids = mysql_escape_string($cids);
    	$param = array();
    	$sql = "select cp.category_id as categoryId, cp.product_id,
    	        pi.id as itemId
				from  $this->tableName pi
				join category_products cp  on cp.product_id = pi.product_id
				where cp.category_id in ( $cids ) and  pi.is_delete = 0 and cp.is_delete = 0";
    	return $this->dbUtil->SelectData($sql, $param);
    }

    /**
     * return inventory
     */
    public function returnInventoryByItemId($itemId, $qyt){
        $param = array($qyt, $itemId);
        $sql = "update $this->tableName set inventory = inventory + ? where id = ? and is_delete=0";
        return $this->dbUtil->ExecuteQuery($sql,$param);
    }

    public function getProductItemsByMetadatasetId($int_metadatasetId, $int_limit=null, $int_pagesize=null, $str_sortby=null, $str_filter=null, $bool_countOnly = false){

        if ($bool_countOnly) {
            $str_sql = "
                SELECT COUNT( DISTINCT `p`.`id` ) AS `rows`
                FROM `auto_metadata_set_{$int_metadatasetId}` AS `a`
                    LEFT JOIN `products` AS `p` ON `a`.`product_id` = `p`.`id` AND `p`.`is_delete` = 0
                    LEFT JOIN `product_items` AS `pi` ON `a`.`product_item_id` = `pi`.`id` AND `pi`.`inventory` > '0' AND `pi`.`sale_price` > '0'
                    LEFT JOIN `product_images` AS `pp` ON `pp`.`id` = `pi`.`default_item_image_id`
                    LEFT JOIN `category_products` AS `cp` ON `p`.`id` = `cp`.`product_id`
                WHERE 1
                    %s
                    AND `pi`.`default_item` > '0'
                    AND `pi`.`is_delete` = '0'
                    ";
//                    AND `pi`.`visible` > '0'
            $str_sql = sprintf($str_sql, $str_filter);
        }else{
            $str_sql = "
                 SELECT
                    `p`.`title`, `p`.`url_title`, `p`.`id` AS `product_id`,
                    `pi`.`id` AS `product_item_id`,
                    `pi`.`cost_price`, `pi`.`list_price`, `pi`.`sale_price`, `pi`.`marketing_price`,
                    UNIX_TIMESTAMP(`pi`.`created`) AS `created`,
                    `pp`.`name` AS `image_name`, `pp`.`folder` AS `image_folder`,
                    ( SELECT COUNT(DISTINCT `q`.`id`) FROM `queries` AS `q`
                        LEFT JOIN `query_replies` AS `qr` ON `qr`.`query_id` = `q`.`id`
                    WHERE `qr`.`id` IS NOT NULL
                        AND `q`.`is_delete` = '0' AND `q`.`product_id` = `p`.`id` ) AS `total_queries`,
                    ( SELECT COUNT(DISTINCT `c`.`id`) FROM `comments` AS `c`
                        LEFT JOIN `comment_replies` AS `cr` ON `cr`.`comment_id` = `c`.`id`
                    WHERE `cr`.`id` IS NOT NULL
                        AND `c`.`is_delete` = '0' AND `c`.`product_id` = `p`.`id` ) AS `total_comments`
                FROM `auto_metadata_set_{$int_metadatasetId}` AS `a`
                    LEFT JOIN `products` AS `p` ON `a`.`product_id` = `p`.`id` AND `p`.`is_delete` = '0'
                    LEFT JOIN `product_items` AS `pi` ON `a`.`product_item_id` = `pi`.`id` AND `pi`.`inventory` > '0' AND `pi`.`sale_price` > '0'
                    LEFT JOIN `product_images` AS `pp` ON `pp`.`id` = `pi`.`default_item_image_id`
                    LEFT JOIN `category_products` AS `cp` ON `p`.`id` = `cp`.`product_id`
                WHERE 1
                    %s
                    AND `pi`.`default_item` > '0'
                    AND `pi`.`is_delete` = '0'
                GROUP BY `p`.`id`
                ORDER BY %s
                LIMIT {$int_limit},{$int_pagesize}
            ";
//                    AND `pi`.`visible` > '0'
            $str_sql = sprintf($str_sql, $str_filter, $str_sortby);
        }

        $arr_results = $this->dbUtil->SelectData($str_sql, array());
        if (!$bool_countOnly)
            return $arr_results;
        return $arr_results[0]['rows'];

    }

    public function getProductItemsBySearch($int_limit=null, $int_pagesize=null, $str_sortby=null, $str_filter=null, $bool_countOnly = false){

        if ($bool_countOnly) {
            $str_sql = "
                SELECT COUNT( DISTINCT `p`.`id` ) AS `rows`
                FROM `products` AS `p`
                    LEFT JOIN `product_items` AS `pi` ON `pi`.`product_id` = `p`.`id` AND `pi`.`inventory` > '0' AND `pi`.`sale_price` > '0'
                    LEFT JOIN `product_images` AS `pp` ON `pp`.`id` = `pi`.`default_item_image_id`
                WHERE 1
                    %s
                    AND `p`.`is_delete` = '0'
                    AND `pi`.`is_delete` = '0'
                    ";
            $str_sql = sprintf($str_sql, $str_filter);
        }else{
            $str_sql = "
                SELECT
                    `p`.`title`, `p`.`url_title`, `p`.`id` AS `product_id`,
                    `pi`.`id` AS `product_item_id`,
                    `pi`.`cost_price`, `pi`.`list_price`, `pi`.`sale_price`, `pi`.`marketing_price`,
                    UNIX_TIMESTAMP(`pi`.`created`) AS `created`,
                    `pp`.`name` AS `image_name`, `pp`.`folder` AS `image_folder`,
                    ( SELECT COUNT(DISTINCT `q`.`id`) FROM `queries` AS `q`
                        LEFT JOIN `query_replies` AS `qr` ON `qr`.`query_id` = `q`.`id`
                    WHERE `qr`.`id` IS NOT NULL
                        AND `q`.`is_delete` = '0' AND `q`.`product_id` = `p`.`id` ) AS `total_queries`,
                    ( SELECT COUNT(DISTINCT `c`.`id`) FROM `comments` AS `c`
                        LEFT JOIN `comment_replies` AS `cr` ON `cr`.`comment_id` = `c`.`id`
                    WHERE `cr`.`id` IS NOT NULL
                        AND `c`.`is_delete` = '0' AND `c`.`product_id` = `p`.`id` ) AS `total_comments`
                 FROM `products` AS `p`
                    LEFT JOIN `product_items` AS `pi` ON `pi`.`product_id` = `p`.`id` AND `pi`.`inventory` > '0' AND `pi`.`sale_price` > '0'
                    LEFT JOIN `product_images` AS `pp` ON `pp`.`id` = `pi`.`default_item_image_id`
                WHERE 1
                    %s
                    AND `p`.`is_delete` = 0
                    AND `pi`.`is_delete` = '0'
                GROUP BY `p`.`id`
                ORDER BY %s
                LIMIT {$int_limit},{$int_pagesize}
            ";
            $str_sql = sprintf($str_sql, $str_filter, $str_sortby);
        }

        $arr_results = $this->dbUtil->SelectData($str_sql, array());
        if (!$bool_countOnly)
            return $arr_results;
        return $arr_results[0]['rows'];

    }

}



